ALTER TABLE AccessLog ADD NodeID UNIQUEIDENTIFIER NOT NULL DEFAULT('00000000-0000-0000-0000-000000000000')
GO

CREATE TABLE DataOperationFailure
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    DataOperationID INT NOT NULL REFERENCES DataOperation(ID),
    FileGroupID INT NOT NULL REFERENCES FileGroup(ID),
    [Log] VARCHAR(1000) NOT NULL,
    StackTrace VARCHAR(MAX) NOT NULL,
    TimeOfFailure DATETIME NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_DataOperationID
ON DataOperationFailure(DataOperationID ASC)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_FileGroupID
ON DataOperationFailure(FileGroupID ASC)
GO

CREATE TABLE SCADAPoint
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    BreakerID INT NOT NULL FOREIGN KEY REFERENCES Asset(ID),
    Point VARCHAR(200) NOT NULL
)
GO

EXEC UniversalCascadeDelete 'DataOperation', 'ID IN (SELECT ID FROM DataOperation WHERE TypeName LIKE ''FaultData.DataOperations.DailyStatisticOperation'''
GO

CREATE NONCLUSTERED INDEX IX_SentEmail_EmailTypeID
ON SentEmail(EmailTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_SentEmail_TimeSent
ON SentEmail(TimeSent ASC)
GO

DROP VIEW ActiveSubscription
GO

CREATE VIEW ActiveSubscription AS
    SELECT
        UserAccountEmailID,
        UserAccountID,
        Approved,
        AssetGroup,
        EmailName,
        Category,
        EmailTypeID,
        Subject,
        LastSent,
        UserName,
        FirstName,
        LastName,
        Email,
        RequireApproval
    FROM (
        SELECT 
            UserAccountEmailType.ID AS UserAccountEmailID,
            UserAccountEmailType.UserAccountID,
            UserAccountEmailType.Approved,
            AssetGroup.Name AS AssetGroup,
            EmailType.Name AS EmailName,
            EmailCategory.Name AS Category,
            EmailType.ID AS EmailTypeID,
            SentEmail.Subject AS Subject,
            SentEmail.TimeSent AS LastSent,
            UserAccount.Name AS UserName,
            UserAccount.FirstName AS FirstName,
            UserAccount.LastName AS LastName,
            UserAccount.Email,
            EmailType.RequireApproval,
            ROW_NUMBER() OVER (
                PARTITION BY UserAccountEmailType.ID
                ORDER BY SentEmail.TimeSent DESC
            ) AS rn
        FROM UserAccountEmailType LEFT JOIN
            AssetGroup ON AssetGroup.ID = UserAccountEmailType.AssetGroupID LEFT JOIN
            EmailType ON UserAccountEmailType.EmailTypeID = EmailType.ID LEFT JOIN
            EmailCategory ON EmailCategory.ID = EmailType.EmailCategoryID LEFT JOIN
            SentEmail ON SentEmail.EmailTypeID = EmailType.ID LEFT JOIN
            UserAccount ON UserAccount.ID = UserAccountEmailType.UserAccountID
    ) AS recent WHERE rn = 1
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.Historian', 'None', 'None')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.PointQuery', 'SELECT NULL AS Point WHERE 1 IS NULL', 'SELECT NULL AS Point WHERE 1 IS NULL')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.QueryTolerance', '4.0', '4.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('SCADA.BreakerOpenValue', '0.0', '0.0')
GO